﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Transactions;
using DataAccessLayer.Properties;
using System.Data.Linq;
using IMModel.Domain;

namespace DataAccessLayer
{
    public partial class SqlDataProvider : IDataProvider
    {
        public void SaveAdmUnit(IEnumerable<DTO.AdmUnit> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Unit unit = new Unit
                        {
                            Name = i.Name
                        };
                        db.Units.InsertOnSubmit(unit);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Units.DeleteOnSubmit(db.Units.First(f => f.IDUnit == d.IDUnit));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Unit unit = db.Units.First(f => f.IDUnit == u.IDUnit);
                        unit.Name = u.Name;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmPersonDegrees(IEnumerable<DTO.AdmPersonDegree> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var p in list.Where(x => x.State == DTO.StateObject.Created))
                    {
                        db.PersonDegrees.InsertOnSubmit(new PersonDegree
                        {
                            IDPerson = p.Person.IDPerson,
                            IDDegree = p.Degree.ID,
                            IDTerm = p.IDTerm
                        });

                        db.SubmitChanges();
                    }

                    foreach (var p in list.Where(x => x.State == DTO.StateObject.Updated))
                    {
                        PersonDegree old = db.PersonDegrees.Single(x => x.ID == p.ID);

                        old.IDPerson = p.Person.IDPerson;
                        old.IDDegree = p.Degree.ID;
                        old.IDTerm = p.IDTerm;

                        db.SubmitChanges();
                    }

                    foreach (var p in list.Where(x => x.State == DTO.StateObject.Deleted))
                    {
                        db.PersonDegrees.DeleteOnSubmit(db.PersonDegrees.Single(x => x.ID == p.ID));

                        db.SubmitChanges();
                    }

                    transaction.Complete();
                }
            }
        }

        public void SaveAdmDegree(IEnumerable<DTO.AdmDegree> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach(var d in list.Where(x => x.State == DTO.StateObject.Created))
                    {
                        db.Degrees.InsertOnSubmit(new Degree
                        {
                            Name = d.Name
                        });

                        db.SubmitChanges();
                    }

                    foreach(var d in list.Where(x => x.State == DTO.StateObject.Updated))
                    {
                        Degree degree = db.Degrees.Single(x => x.IDDegree == d.ID);
                        degree.Name = d.Name;

                        db.SubmitChanges();
                    }

                    foreach (var d in list.Where(x => x.State == DTO.StateObject.Deleted))
                    {
                        db.Degrees.DeleteOnSubmit(db.Degrees.Single(x => x.IDDegree == d.ID));

                        db.SubmitChanges();
                    }

                    transaction.Complete();
                }
            }
        }

        public void SaveAdmTerm(IEnumerable<DTO.AdmTerm> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Term term = new Term
                        {
                            StartFill = i.StartFill,
                            StopFill = i.StopFill,
                            Order = i.Order,
                            Founds = i.Founds,
                            k1 = i.k1,
                            k2 = i.k2,
                            l1 = i.l1,
                            l2 = i.l2
                        };
                        db.Terms.InsertOnSubmit(term);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Terms.DeleteOnSubmit(db.Terms.First(f => f.IDTerm == d.IDTerm));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Term term = db.Terms.First(f => f.IDTerm == u.IDTerm);
                        term.Start = u.Start;
                        term.Stop = u.Stop;
                        term.StartFill = u.StartFill;
                        term.StopFill = u.StopFill;
                        term.Order = u.Order;
                        term.Founds = u.Founds;
                        term.k1 = u.k1;
                        term.k2 = u.k2;
                        term.l1 = u.l1;
                        term.l2 = u.l2;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmPerson(IEnumerable<DTO.AdmPerson> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Person person = new Person
                        {
                            FirstName = i.FirstName,
                            LastName = i.LastName,
                            Patronymic = i.Patronymic,
                            E_Mail = i.EMail
                        };
                        db.Persons.InsertOnSubmit(person);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Persons.DeleteOnSubmit(db.Persons.First(f => f.IDPerson == d.IDPerson));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Person person = db.Persons.First(f => f.IDPerson == u.IDPerson);
                        person.FirstName = u.FirstName;
                        person.LastName = u.LastName;
                        person.Patronymic = u.Patronymic;
                        person.E_Mail = u.EMail;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmJob(IEnumerable<DTO.AdmJob> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Job job = new Job
                        {
                            IDPosition = i.Position.IDPosition,
                            IDStatus = i.Status.IDStatus
                        };
                        db.Jobs.InsertOnSubmit(job);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Jobs.DeleteOnSubmit(db.Jobs.First(f => f.IDJob == d.IDJob));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Job job = db.Jobs.First(f => f.IDJob == u.IDJob);
                        job.IDPosition = u.Position.IDPosition;
                        job.IDStatus = u.Status.IDStatus;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmStatus(IEnumerable<DTO.AdmStatus> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Status status = new Status
                        {
                            Order = i.Order
                        };
                        db.Status.InsertOnSubmit(status);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Status.DeleteOnSubmit(db.Status.First(f => f.IDStatus == d.IDStatus));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Status status = db.Status.First(f => f.IDStatus == u.IDStatus);
                        status.Order = u.Order;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmPosition(IEnumerable<DTO.AdmPosition> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Position position = new Position
                        {
                            Name = i.Name,
                            SID = i.SID,
                            Order = i.Order,
                            IsTotal = i.IsTotal
                        };
                        db.Positions.InsertOnSubmit(position);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Positions.DeleteOnSubmit(db.Positions.First(f => f.IDPosition == d.IDPosition));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Position position = db.Positions.First(f => f.IDPosition == u.IDPosition);
                        position.Name = u.Name;
                        position.SID = u.SID;
                        position.Order = u.Order;
                        position.IsTotal = u.IsTotal;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmResponsible(IEnumerable<DTO.AdmResponsible> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var r in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Responsible resp = new Responsible
                        {
                            IDDepartment = r.Department.IDDepartment,
                            IDPosition = r.Position.IDPosition
                        };
                        db.Responsibles.InsertOnSubmit(resp);
                        db.SubmitChanges();
                    }
                    foreach (var r in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Responsibles.DeleteOnSubmit(db.Responsibles.First(f => f.IDResponsible == r.IDResponsible));
                        db.SubmitChanges();
                    }
                    foreach (var r in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Responsible resp = db.Responsibles.First(f => f.IDResponsible == r.IDResponsible);
                        if (r.Department != null)
                            resp.IDDepartment = r.Department.IDDepartment;
                        else
                            resp.IDDepartment = null;
                        resp.IDPosition = r.Position.IDPosition;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmCharacteristic(IEnumerable<DTO.AdmCharacteristic> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Characteristic characteristic = new Characteristic
                        {
                            Name = c.Name,
                            //Order = c.Order
                        };
                        db.Characteristics.InsertOnSubmit(characteristic);
                        db.SubmitChanges();
                    }
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Characteristics.DeleteOnSubmit(db.Characteristics.First(f => f.IDCharacteristic == c.ID));
                        db.SubmitChanges();
                    }
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Characteristic characteristic = db.Characteristics.First(f => f.IDCharacteristic == c.ID);
                        characteristic.Name = c.Name;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmDepartment(IEnumerable<DTO.AdmDepartment> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var d in list.Where(f => f.State == DTO.StateObject.Created))
                    {
                        Department department = new Department
                        {
                            Name = d.Name,
                            Abbriviation = d.Abbriviation,
                            IDDepartmentType = d.DepartmentType.ID
                        };
                        db.Departments.InsertOnSubmit(department);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Departments.DeleteOnSubmit(db.Departments.First(f => f.IDDepartment == d.IDDepartment));
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Department department = db.Departments.First(f => f.IDDepartment == d.IDDepartment);
                        department.Name = d.Name;
                        department.Abbriviation = d.Abbriviation;
                        department.IDDepartmentType = d.DepartmentType.ID;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmSection(IEnumerable<DTO.AdmSection> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var s in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Section section = new Section
                        {
                            Name = s.Name
                        };
                        db.Sections.InsertOnSubmit(section);
                        db.SubmitChanges();
                    }
                    foreach (var s in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Sections.DeleteOnSubmit(db.Sections.First(f => f.IDSection == s.ID));
                        db.SubmitChanges();
                    }
                    foreach (var s in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Section section = db.Sections.First(f => f.IDSection == s.ID);
                        section.Name = s.Name;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmGradation(IEnumerable<DTO.AdmGradation> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var g in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Gradation gradation = new Gradation
                        {
                            Name = g.Name
                        };
                        db.Gradations.InsertOnSubmit(gradation);
                        db.SubmitChanges();
                    }
                    foreach (var g in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Gradation gradation = db.Gradations.First(f => f.IDGradation == g.ID);
                        gradation.Name = g.Name;
                        db.SubmitChanges();
                    }
                    foreach (var g in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Gradations.DeleteOnSubmit(db.Gradations.First(f => f.IDGradation == g.ID));
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmCategory(IEnumerable<DTO.AdmCategory> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Category category = new Category
                        {
                            Name = c.Name,
                            ImagePath = c.ImagePath
                        };
                        db.Categories.InsertOnSubmit(category);
                        db.SubmitChanges();
                    }
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Category category = db.Categories.First(f => f.IDCategory == c.IDCategory);
                        category.Name = c.Name;
                        category.ImagePath = c.ImagePath;
                        db.SubmitChanges();
                    }
                    foreach (var c in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Categories.DeleteOnSubmit(db.Categories.First(f => f.IDCategory == c.IDCategory));
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// Сохранение категорий и их весов
        /// </summary>
        /// <param name="list"></param>
        public void SaveAdmlCategoriesByTerm(IEnumerable<DTO.AdmCategory> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        CategoryTerm categoryTerm = new CategoryTerm
                        {
                            IDCategory = i.IDCategory,
                            IDTerm = i.IDTerm,
                            Weight = i.Weight
                        };
                        db.CategoryTerms.InsertOnSubmit(categoryTerm);
                        db.SubmitChanges();
                    }
                    foreach (var d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.CategoryTerms.DeleteOnSubmit(db.CategoryTerms.First(f => f.IDCategory == d.IDCategory && f.IDTerm == d.IDTerm));
                        db.SubmitChanges();
                    }
                    foreach (var u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        CategoryTerm categoryTerm = db.CategoryTerms.First(f => f.IDCategory == u.IDCategory && f.IDTerm == u.IDTerm);
                        categoryTerm.Weight = u.Weight;
                        db.SubmitChanges();
                    }

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// Сохранение структуры ИС
        /// В КАЧЕСТВЕ СОЗДАННЫХ, УДАЛЕННЫХ И МОДИФИЦИРОВАННЫХ ГРАДАЦИЙ ДОЛЖНЫ ПОДАВАТЬСЯ ГРАДАЦИИ ТОЛЬКО НИЖНЕГО УРОВНЯ
        /// </summary>
        /// <param name="list"></param>
        public void SaveIndexMarkStruct(IEnumerable<DTO.DTOBase> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                IEnumerable<DTO.AdmSection> Sections = list.Where(w => w.GetType() == typeof(DTO.AdmSection)).Cast<DTO.AdmSection>();
                IEnumerable<DTO.AdmCharacteristic> Characteristics = list.Where(w => w.GetType() == typeof(DTO.AdmCharacteristic)).Cast<DTO.AdmCharacteristic>();
                IEnumerable<DTO.AdmGradation> Gradations = list.Where(w => w.GetType() == typeof(DTO.AdmGradation)).Cast<DTO.AdmGradation>();

                Dictionary<DTO.AdmCharacteristic, CharacteristicTerm> createdCharacteristics = new Dictionary<DTO.AdmCharacteristic, CharacteristicTerm>();
                Dictionary<DTO.AdmSection, SectionTerm> createdSections = new Dictionary<DTO.AdmSection, SectionTerm>();

                using (var transaction = new TransactionScope())
                {
                    // СНАЧАЛА НЕОБХОДИМО ОБРАБОТАТЬ ВСЕ РАЗДЕЛЫ, А УЖЕ ПОТОМ ОБРАБАТЫВАТЬ ПОКАЗАТЕЛИ
                    #region РАЗДЕЛЫ

                    foreach (DTO.AdmSection s in Sections.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        SectionTerm old_sectionTerm = db.SectionTerms.First(f => f.IDSectionTerm == s.IDSectionTerm);
                        // Сохраним новый IDSection, на тот случай, если была выполнена замена узла
                        old_sectionTerm.IDSection = s.ID;
                        old_sectionTerm.Weight = s.Weight;
                        old_sectionTerm.Order = s.Order;
                        old_sectionTerm.Participate = s.Participate;

                        if (s.ParentSection != null)
                        {
                            if (createdSections.Keys.Contains(s.ParentSection))
                                old_sectionTerm.SectionTerm1 = createdSections[s.ParentSection];
                            else
                                old_sectionTerm.IDParentSectionTerm = s.ParentSection.IDSectionTerm;
                        }

                        db.SubmitChanges();
                    }
                    foreach (DTO.AdmSection s in Sections.Where(w => w.State == DTO.StateObject.Created))
                    {
                        SectionTerm new_sectionTerm = new SectionTerm
                        {
                            IDSection = s.ID,
                            IDTerm = s.IDTerm,
                            Weight = s.Weight,
                            Order = s.Order,
                            Participate = s.Participate
                        };

                        if (s.ParentSection != null)
                        {
                            if (createdSections.Keys.Contains(s.ParentSection))
                                new_sectionTerm.SectionTerm1 = createdSections[s.ParentSection];
                            else
                                new_sectionTerm.IDParentSectionTerm = s.ParentSection.IDSectionTerm;
                        }

                        createdSections.Add(s, new_sectionTerm);
                        db.SectionTerms.InsertOnSubmit(new_sectionTerm);
                        // Вызовем сохранение, чтобы обновились IDSectionTerm. Это необходимо для поиска ID в CategorySection по IDSectionTerm и IDCategoryTerm
                        db.SubmitChanges();
                    }
                    foreach (DTO.AdmSection s in Sections.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        this.DeleteAdmSectionRelations(s);
                    }
                    #endregion

                    #region ПОКАЗАТЕЛИ
                    // Обновляем показатели
                    foreach (DTO.AdmCharacteristic c in Characteristics.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        CharacteristicTerm charTerm = db.CharacteristicTerms.First(f => f.IDCharacteristicTerm == c.IDCharacteristicTerm);
                        // Сохраним новый IDCharacteristic, на тот случай, если была выполнена замена узла
                        charTerm.IDCharacteristic = c.ID;
                        charTerm.Weight = c.Weight;
                        charTerm.Order = c.Order;
                        charTerm.Visibility = c.Visibility;
                        charTerm.IDUnit = c.Unit.IDUnit;
                        charTerm.IndexRequired = c.IndexRequired;
                        charTerm.VolumeValidPeriod = c.VolumeValidPeriod;
                        charTerm.IDPrevCharacteristicTerm = c.IDPrevCharacteristicTerm;

                        if (createdSections.Keys.Contains(c.Section))
                            charTerm.IDCategorySectionTerm = this.GetIDCategorySectionTerm(createdSections[c.Section].IDSectionTerm, 
                                c.GetCategory().IDCategoryTerm);
                        else
                            charTerm.IDCategorySectionTerm = this.GetIDCategorySectionTerm(c.Section.IDSectionTerm, 
                                c.GetCategory().IDCategoryTerm);

                        db.SubmitChanges();


                        // Обновляем ответственных за достоверность данных
                        // Сначала удалим все старые записи в базе данных
                        var responsibles = from ct in db.CharacteristicTerms
                                           join cr in db.CharacteristicResponsibles on ct.IDCharacteristicTerm equals cr.IDCharacteristicTerm
                                           where ct.IDCharacteristicTerm == c.IDCharacteristicTerm
                                           select cr;
                        db.CharacteristicResponsibles.DeleteAllOnSubmit(responsibles);
                        db.SubmitChanges();

                        foreach (DTO.AdmResponsible resp in c.Responsibles)
                        {
                            CharacteristicResponsible charResp = new CharacteristicResponsible
                            {
                                IDCharacteristicTerm = c.IDCharacteristicTerm,
                                IDResponsible = resp.IDResponsible
                            };
                            db.CharacteristicResponsibles.InsertOnSubmit(charResp);
                        }

                        db.SubmitChanges();
                    }

                    // Создаем показатели
                    foreach (DTO.AdmCharacteristic c in Characteristics.Where(w => w.State == DTO.StateObject.Created))
                    {
                        CharacteristicTerm charTerm = new CharacteristicTerm
                        {
                            IDCharacteristic = c.ID,
                            IDTerm = c.IDTerm,
                            Weight = c.Weight,
                            Order = c.Order,
                            Visibility = c.Visibility,
                            IDUnit = c.Unit.IDUnit,
                            IndexRequired = c.IndexRequired,
                            VolumeValidPeriod = c.VolumeValidPeriod,
                            IDPrevCharacteristicTerm = c.IDPrevCharacteristicTerm
                        };

                        if (createdSections.Keys.Contains(c.Section))
                            charTerm.IDCategorySectionTerm = this.GetIDCategorySectionTerm(createdSections[c.Section].IDSectionTerm, 
                                c.GetCategory().IDCategoryTerm);
                        else
                            charTerm.IDCategorySectionTerm = this.GetIDCategorySectionTerm(c.Section.IDSectionTerm, 
                                c.GetCategory().IDCategoryTerm);

                        createdCharacteristics.Add(c, charTerm);
                        db.CharacteristicTerms.InsertOnSubmit(charTerm);

                        foreach (DTO.AdmResponsible responsible in c.Responsibles)
                        {
                            CharacteristicResponsible charResp = new CharacteristicResponsible
                            {
                                CharacteristicTerm = charTerm,
                                IDResponsible = responsible.IDResponsible
                            };
                            db.CharacteristicResponsibles.InsertOnSubmit(charResp);
                        }

                        // Сохраним изменения, чтобы обновились ID
                        db.SubmitChanges();
                    }
                    foreach (DTO.AdmCharacteristic c in Characteristics.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        this.DeleteAdmCharacteristicRelations(c);
                    }
                    #endregion

                    #region ГРАДАЦИИ
                    foreach (DTO.AdmGradation g in Gradations.Where(w => w.State == DTO.StateObject.Created))
                    {
                        CharacteristicGroup gChar = new CharacteristicGroup
                        {
                            IDGradationGroupTerm = g.IDGradationTerm,
                            IsNecessary = g.IsNecessary
                        };
                        if (createdCharacteristics.Keys.Contains(g.ParentCharacteristic))
                            gChar.CharacteristicTerm = createdCharacteristics[g.ParentCharacteristic];
                        else
                            gChar.IDCharacteristicTerm = g.ParentCharacteristic.IDCharacteristicTerm;

                        db.CharacteristicGroups.InsertOnSubmit(gChar);
                    }

                    foreach (DTO.AdmGradation g in Gradations.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.CharacteristicGroups.DeleteAllOnSubmit(
                            db.CharacteristicGroups.Where(
                                s => s.IDCharacteristicTerm == g.ParentCharacteristic.IDCharacteristicTerm
                                    && s.IDGradationGroupTerm == g.IDGradationTerm));

                        // Обязательно необходимо сохранить изменения
                        db.SubmitChanges();
                    }

                    foreach (DTO.AdmGradation g in Gradations.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        CharacteristicGroup cg = db.CharacteristicGroups.Single(x => x.IDCharacteristicTerm == g.ParentCharacteristic.IDCharacteristicTerm
                            && x.IDGradationGroupTerm == g.IDGradationTerm);

                        cg.IsNecessary = g.IsNecessary;

                        db.SubmitChanges();
                    }
                    #endregion

                    // Сохраняем изменения
                    db.SubmitChanges();

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        /// <summary>
        /// Сохранение структуры УГТУ
        /// </summary>
        /// <param name="list"></param>
        public void SaveUniversityStruct(IEnumerable<DTO.DTOBase> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                IEnumerable<DTO.AdmDepartment> Departments = list.Where(w => w.GetType() == typeof(DTO.AdmDepartment)).Cast<DTO.AdmDepartment>();
                IEnumerable<DTO.AdmRespondent> Respondents = list.Where(w => w.GetType() == typeof(DTO.AdmRespondent)).Cast<DTO.AdmRespondent>();

                Dictionary<DTO.AdmDepartment, DepartmentTerm> createdDepartments = new Dictionary<DTO.AdmDepartment, DepartmentTerm>();

                using (var transaction = new TransactionScope())
                {
                    #region ОТДЕЛЫ
                    // Создание
                    foreach (DTO.AdmDepartment d in Departments.Where(w => w.State == DTO.StateObject.Created))
                    {
                        DepartmentTerm departmentTerm = new DepartmentTerm
                        {
                            IDDepartment = (d.IDDepartment ?? 0),
                            IDTerm = d.IDTerm
                        };

                        if (d.ParentDepartment != null)
                        {
                            if (createdDepartments.Keys.Contains(d.ParentDepartment))
                                departmentTerm.DepartmentTerm1 = createdDepartments[d.ParentDepartment];
                            else
                                departmentTerm.IDParentDepartmentTerm = d.ParentDepartment.IDDepartmentTerm;
                        }

                        createdDepartments.Add(d, departmentTerm);
                        db.DepartmentTerms.InsertOnSubmit(departmentTerm);

                        // Сохраним изменения, чтобы обновились ID
                        db.SubmitChanges();
                    }

                    // Обновление
                    foreach (DTO.AdmDepartment d in Departments.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        DepartmentTerm depTerm = db.DepartmentTerms.Single(s => s.IDDepartmentTerm == d.IDDepartmentTerm);
                        // Необходимо для замены элемента
                        depTerm.IDDepartment = (d.IDDepartment ?? 0);

                        if (d.ParentDepartment != null)
                        {
                            if (createdDepartments.Keys.Contains(d.ParentDepartment))
                                depTerm.DepartmentTerm1 = createdDepartments[d.ParentDepartment];
                            else
                                depTerm.IDParentDepartmentTerm = d.ParentDepartment.IDDepartmentTerm;
                        }

                        db.SubmitChanges();
                    }

                    // Удаление
                    foreach (DTO.AdmDepartment d in Departments.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        this.DeleteAdmDepartmentRelations(d);
                    }
                    #endregion

                    #region РЕСПОНДЕНТЫ
                    // Создание
                    foreach (DTO.AdmRespondent r in Respondents.Where(w => w.State == DTO.StateObject.Created))
                    {
                        Respondent respondent = new Respondent
                        {
                            IDPerson = r.Person.IDPerson,
                            IDJob = r.Job.IDJob,
                            FlagOsnJob = r.FlagOsnJob,
                            IgnoreVolumes = r.IgnoreVolumes,
                            Dismissed = r.Dismissed
                        };

                        if (createdDepartments.Keys.Contains(r.Department))
                            respondent.IDDepartmentTerm = createdDepartments[r.GetDepartment()].IDDepartmentTerm;
                        else
                            respondent.IDDepartmentTerm = r.GetDepartment().IDDepartmentTerm;

                        db.Respondents.InsertOnSubmit(respondent);
                    }
                    // Обновление
                    foreach (DTO.AdmRespondent r in Respondents.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        Respondent respondent = db.Respondents.First(f => f.IDRespondent == r.IDRespondent);
                        respondent.IDPerson = r.Person.IDPerson;
                        respondent.IDJob = r.Job.IDJob;
                        respondent.FlagOsnJob = r.FlagOsnJob;
                        respondent.IgnoreVolumes = r.IgnoreVolumes;
                        respondent.Dismissed = r.Dismissed;

                        if (createdDepartments.Keys.Contains(r.Department))
                            respondent.IDDepartmentTerm = createdDepartments[r.GetDepartment()].IDDepartmentTerm;
                        else
                            respondent.IDDepartmentTerm = r.GetDepartment().IDDepartmentTerm;

                        db.SubmitChanges();
                    }
                    // Удаление
                    foreach (DTO.AdmRespondent r in Respondents.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.Respondents.DeleteAllOnSubmit(db.Respondents.Where(w => w.IDRespondent == r.IDRespondent));
                        db.SubmitChanges();
                    }
                    #endregion

                    // Сохраним изменения
                    db.SubmitChanges();

                    // Фиксируем транзакцию
                    transaction.Complete();
                }
            }
        }

        public void SaveAdmGradationHierarhial(IEnumerable<DTO.AdmGradation> list)
        {
            Dictionary<DTO.AdmGradation, GradationTerm> createdObjects = new Dictionary<DTO.AdmGradation, GradationTerm>();

            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    // Сначала необходимо создать добавленные элементы
                    #region Создание элементов
                    foreach (DTO.AdmGradation i in list.Where(w => w.State == DTO.StateObject.Created))
                    {
                        GradationTerm grel = new GradationTerm
                        {
                            Weight = i.Weight,
                            Order = i.Order,
                            IDGradation = i.ID,
                            IDTerm = i.IDTerm
                        };
                        if (i.Unit != null)
                            grel.IDUnit = i.Unit.IDUnit;
                        else
                            grel.IDUnit = null;

                        createdObjects.Add(i, grel);
                    }
                    // Создаем связи между новыми элементами
                    foreach (KeyValuePair<DTO.AdmGradation, GradationTerm> val in createdObjects)
                    {
                        if (val.Key.ParentGradation != null)
                        {
                            if (createdObjects.Keys.Contains(val.Key.ParentGradation))
                                val.Value.GradationTerm1 = createdObjects[val.Key.ParentGradation];
                            else
                                val.Value.GradationTerm1 = db.GradationTerms.Single(s => s.IDGradationTerm == val.Key.ParentGradation.IDGradationTerm);
                        }
                        db.GradationTerms.InsertOnSubmit(val.Value);
                    }
                    db.SubmitChanges();
                    #endregion
                    foreach (DTO.AdmGradation u in list.Where(w => w.State == DTO.StateObject.Updated))
                    {
                        GradationTerm grel = db.GradationTerms.Single(s => s.IDGradationTerm == u.IDGradationTerm);
                        grel.Weight = u.Weight;
                        // Необходимо для замены элемента
                        grel.Order = u.Order;
                        grel.IDGradation = u.ID;
                        grel.IDTerm = u.IDTerm;
                        // Теперь обновим связь родитель-потомок
                        if (u.ParentGradation != null)
                        {
                            if (createdObjects.Keys.Contains(u.ParentGradation))
                                grel.GradationTerm1 = createdObjects[u.ParentGradation];
                            else
                                grel.GradationTerm1 = db.GradationTerms.Single(s => s.IDGradationTerm == u.ParentGradation.IDGradationTerm);
                        }

                        if (u.Unit != null)
                            grel.IDUnit = u.Unit.IDUnit;
                        else
                            grel.IDUnit = null;

                        db.SubmitChanges();
                    }
                    foreach (DTO.AdmGradation d in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        // Игнорируем градации, которые отсутствуют в базе
                        if (d.IDGradationTerm == 0) continue;

                        db.DeletingGradationTerm(d.IDGradationTerm);
                    }
                    db.SubmitChanges();

                    transaction.Complete();
                }
            }
        }

        public void SaveAnswerLog(IEnumerable<DTO.DTOBase> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                IEnumerable<DTO.AdmAnswerLog> ans = list.Where(x => x.GetType() == typeof(DTO.AdmAnswerLog)).Cast<DTO.AdmAnswerLog>();
                IEnumerable<DTO.AdmGradationAnswerLog> ansGrad = list.Where(x => x.GetType() == typeof(DTO.AdmGradationAnswerLog)).Cast<DTO.AdmGradationAnswerLog>();

                using (var transaction = new TransactionScope())
                {
                    foreach (DTO.AdmAnswerLog a in ans)
                    {
                        db.DeletingAnswer(a.IDAnswer);
                    }

                    foreach (DTO.AdmGradationAnswerLog g in ansGrad)
                    {
                        db.GradationsAnswers.DeleteOnSubmit(
                            db.GradationsAnswers.Single(x => x.IDAnswer == g.IDAnswer && x.IDGradationTerm == g.IDGradationTerm));
                    }

                    db.SubmitChanges();

                    transaction.Complete();
                }
            }
        }

        public void SaveInputLog(IEnumerable<DTO.AdmInputLog> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                using (var transaction = new TransactionScope())
                {
                    foreach (var item in list.Where(w => w.State == DTO.StateObject.Deleted))
                    {
                        db.InputLogs.DeleteOnSubmit(db.InputLogs.First(f => f.IDLog == item.IDLog));
                    }

                    db.SubmitChanges();

                    transaction.Complete();
                }
            }
        }

        public void SaveAdmDepartmentTypes(IEnumerable<DTO.AdmDepartmentType> list)
        {
            using (IMModel.Domain.IMDataDataContext db = new IMModel.Domain.IMDataDataContext(Settings.Default.IndexMarkConnectionString))
            {
                foreach (DTO.AdmDepartmentType dt in list.Where(w => w.State == DTO.StateObject.Created))
                {
                    DepartmentType new_dt = new DepartmentType
                    {
                        Name = dt.Name
                    };
                    db.DepartmentTypes.InsertOnSubmit(new_dt);
                    db.SubmitChanges();
                }
                foreach (DTO.AdmDepartmentType dt in list.Where(w => w.State == DTO.StateObject.Updated))
                {
                    DepartmentType old_dt = db.DepartmentTypes.Single(s => s.ID == dt.ID);
                    old_dt.Name = dt.Name;

                    db.SubmitChanges();
                }
                foreach (DTO.AdmDepartmentType dt in list.Where(w => w.State == DTO.StateObject.Deleted))
                {
                    db.DepartmentTypes.DeleteOnSubmit(db.DepartmentTypes.Single(s => s.ID == dt.ID));
                    db.SubmitChanges();
                }
            }
        }
    }
}
